package service;

import com.fasterxml.jackson.databind.ObjectMapper;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
import java.util.*;

@WebServlet("/student-gpa-recommendations")
public class PMSTU extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        List<StudentRecommendation> recommendations = getCFRecommendations();

        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(recommendations);

        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);
    }

    private List<StudentRecommendation> getCFRecommendations() {
        List<StudentRecommendation> recommendations = new ArrayList<>();
        String url = "jdbc:mysql://192.168.244.11:3306/stats?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


        String query = "SELECT name, semester, predicted_gpa AS predictedGPA " +
                "FROM gpa_recommendations1 " +
                "ORDER BY predicted_gpa DESC " +
                "LIMIT 6";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                String name = rs.getString("name");
                int semester = rs.getInt("semester");
                double predictedGPA = rs.getDouble("predictedGPA"); // 使用AS别名

                recommendations.add(new StudentRecommendation(name, semester, predictedGPA));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return recommendations;
    }

    // 内部类保持不变
    private static class StudentRecommendation {
        private String name;
        private int semester;
        private double predictedGPA;

        public StudentRecommendation(String name, int semester, double predictedGPA) {
            this.name = name;
            this.semester = semester;
            this.predictedGPA = predictedGPA;
        }

        public String getName() { return name; }
        public int getSemester() { return semester; }
        public double getPredictedGPA() { return predictedGPA; }
    }
}